package me.seu.demo.service.operate;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 开工模型 V1
 *
 * @author liangfeihu
 * @since 2022/9/5 11:43
 */
@Slf4j
public class OpUtilV1 {

    public static List<Integer> getExcelDataList(XSSFWorkbook workbook) throws Exception {
        // 获得当前sheet工作表
        XSSFSheet sheet = workbook.getSheetAt(0);
        if (sheet == null) {
            throw new RuntimeException("sheet not allow null");
        }
        // 获得当前sheet的开始行
        int firstRowNum = sheet.getFirstRowNum();

        // 获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum < 2) {
            throw new RuntimeException("no sheet data to handle");
        }
        log.info("excel firstRowNum={} lastRowNum={}", firstRowNum, lastRowNum);

        List<Integer> dataList = new ArrayList<>();
        for (int index = 1; index <= lastRowNum; index++) {
            Row dataRow = sheet.getRow(index);
            if (dataRow == null) {
                continue;
            }

            for (int accIndex = 0; accIndex <= 17; accIndex++) {
                Cell cell = dataRow.getCell(accIndex);
                String strValue = POIUtil.getCellValue(cell);
                try {
                    Integer integerValue = Integer.valueOf(strValue);
                    dataList.add(integerValue);
                } catch (Exception e) {
                    log.error("parse error, strValue={}", strValue, e);
                }
            }
        }

        return dataList;
    }


    public static Map<Integer, List<Integer>> getExcelDataMap(XSSFWorkbook workbook) throws Exception {
        // 获得当前sheet工作表
        XSSFSheet sheet = workbook.getSheetAt(0);
        if (sheet == null) {
            throw new RuntimeException("sheet not allow null");
        }
        // 获得当前sheet的开始行
        int firstRowNum = sheet.getFirstRowNum();

        // 获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum < 2) {
            throw new RuntimeException("no sheet data to handle");
        }
        log.info("excel firstRowNum={} lastRowNum={}", firstRowNum, lastRowNum);

        Map<Integer, List<Integer>> accMap = new HashMap<>();
        for (int index = 1; index <= lastRowNum; index++) {
            Row dataRow = sheet.getRow(index);
            if (dataRow == null) {
                continue;
            }

            List<Integer> accList = new ArrayList<>();
            for (int accIndex = 0; accIndex <= 17; accIndex++) {
                Cell cell = dataRow.getCell(accIndex);
                String strValue = POIUtil.getCellValue(cell);
                try {
                    Integer integerValue = Integer.valueOf(strValue);
                    accList.add(integerValue);
                } catch (Exception e) {
                    log.error("parse error, strValue={}", strValue, e);
                }
            }

            accMap.put(index, accList);
        }

        return accMap;
    }

    /**
     * 计算两数差分取绝对值
     */
    private static Integer getDiff(Integer b1, Integer b2) {
        return Math.abs(b2 - b1);
    }

    /**
     * 计算数组差分取绝对值
     */
    private static List<Integer> getDiffArray(List<Integer> list) {
        List<Integer> result = new ArrayList<>();
        for (int i = 0; i < list.size() - 1; i++) {
            Integer diff = getDiff(list.get(i + 1), list.get(i));
            result.add(diff);
        }
        return result;
    }

    /**
     * 获取数组中最大最小值差值
     */
    private static Integer getMaxMinDiff(List<Integer> array) {
        int max = array.get(0);
        int min = array.get(0);
        for (Integer value : array) {
            if (value > max) {
                max = value;
            }
            if (value < min) {
                min = value;
            }
        }
        return max - min;
    }

    /**
     * 获取数组差分占比列表
     */
    public static List<BigDecimal> getDiffPartList(List<Integer> array) {
        BigDecimal maxDiff = new BigDecimal(getMaxMinDiff(array));
        List<Integer> diffList = getDiffArray(array);

        List<BigDecimal> result = new ArrayList<>();
        for (int i = 0; i < diffList.size(); i++) {
            try {
                BigDecimal percent = new BigDecimal(diffList.get(i)).divide(maxDiff, 2, RoundingMode.HALF_UP);
                result.add(percent);
            } catch (Exception e) {
                log.error("error index={}", i, e);
            }
        }
        return result;
    }

    public static List<Integer> lineNumList(List<Integer> pointList) {
        List<Integer> lineNumList = new ArrayList<>();
        for (Integer point : pointList) {
            Integer lineNum = point / 18 + 1;
            lineNumList.add(lineNum);
        }
        return lineNumList;
    }

    public static void main2(String[] args) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook(OpUtilV1.class.getClassLoader().getResourceAsStream("excel/oee3_cut.xlsx"));
        // 1. 读取Excel数据
        List<Integer> dataList = getExcelDataList(workbook);

        // 波动阈值
        BigDecimal stop = new BigDecimal("0.1");
        // 开工采样时长
        int opNum = 18;

        // 2.获取数组差分占比列表
        List<BigDecimal> list = getDiffPartList(dataList);
        System.out.println(list);

        // 3.找出稳定的段
        List<Point> segmentList = new ArrayList<>();
        int index = 0;
        int firstPoint = 0;
        while (index < list.size()) {
            BigDecimal current = list.get(index);
            if (current.compareTo(stop) < 0) {
                firstPoint = index;

                int j = index + 1;
                while (j < list.size()) {
                    BigDecimal currValue = list.get(j);
                    if (currValue.compareTo(stop) < 0) {
                        j++;
                    } else {
                        break;
                    }
                }

                if (j == list.size()) {
                    if (j - firstPoint >= opNum) {
                        segmentList.add(new Point(firstPoint, j - 1));
                    }
                } else {
                    BigDecimal end = list.get(j - 1);
                    BigDecimal next = list.get(j);
                    if (end.compareTo(stop) < 0 && next.compareTo(stop) >= 0 && j - firstPoint >= opNum) {
                        segmentList.add(new Point(firstPoint, j - 1));
                    }
                }

                index = j;
            }
            index++;
        }
        System.out.println(segmentList);

        // 4. 获取稳定段的平均值，平均值最小的段作为静止段
        List<List<Integer>> opDataList = new ArrayList<>();
        List<Long> averageList = new ArrayList<>();
        for (Point point : segmentList) {
            List<Integer> opData = dataList.subList(point.getStart(), point.getEnd());
            opDataList.add(opData);
            System.out.println(opData);
            Long total = opData.stream().mapToLong(Integer::intValue).sum();
            averageList.add(total / opData.size());
        }
        System.out.println(averageList);

        Integer minIndex = 0;
        Long minAverageValue = averageList.get(0);
        for (int m = 1; m < averageList.size(); m++) {
            if (averageList.get(m) < minAverageValue) {
                minAverageValue = averageList.get(m);
                minIndex = m;
            }
        }
        System.out.println(minIndex + " : " + minAverageValue);
        // 5. 求开工阈值：K = S + ∆S
        List<Integer> opdata = opDataList.get(minIndex);
        Integer opMaxValue = opdata.get(0);
        Integer opMinValue = opdata.get(0);
        for (Integer data : opdata) {
            if (data > opMaxValue) {
                opMaxValue = data;
            }
            if (data < opMinValue) {
                opMinValue = data;
            }
        }
        System.out.println(opdata);
        System.out.println(opMaxValue + " : " + opMinValue);
        //Integer K = opMaxValue + (opMaxValue - opMinValue);
        Integer K = minAverageValue.intValue() + (opMaxValue - opMinValue);
        System.out.println("开工阈值：" + K);


        // 4.获取波动大的点对应的行号
        // todo 判断开工、静止
        //List<Integer> lineNumList = lineNumList(pointList);
        //System.out.println(lineNumList);

        // 5. 输出判断结果到excel
        //outputHandleExcel(workbook, lineNumList);
    }

    public static void outputHandleExcel(XSSFWorkbook workbook, Integer workThreshold, Integer maxMinDiff, String fileName) throws Exception {
        Map<Integer, List<Integer>> excelDataMap = getExcelDataMap(workbook);

        // 获得当前sheet工作表
        XSSFSheet sheet = workbook.getSheetAt(0);
        // 获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();
        for (int index = 1; index <= lastRowNum; index++) {
            Row dataRow = sheet.getRow(index);
            if (dataRow == null) {
                continue;
            }

            List<Integer> dataList = excelDataMap.get(index);
            dataRow.createCell(19).setCellValue(OpModelUtil.judgeWorkStatus(dataList, workThreshold, maxMinDiff) ? "1" : "0");
        }

        //导出excel文件
        try {
            File file = new File(fileName);
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            workbook.write(fileOutputStream);
            fileOutputStream.close();
            System.out.println("========文件导出已完成========");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("IO Erro" + e.getMessage());
        }
        workbook.close();
    }


    public static void main(String[] args) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook(OpUtilV1.class.getClassLoader().getResourceAsStream("excel/oee2_cut.xlsx"));
        // 1. 读取Excel数据
        List<Integer> dataList = getExcelDataList(workbook);

        // 波动阈值
        BigDecimal stop = new BigDecimal("0.15");
        // 开工采样时长
        Integer opNum = 18;

        Integer maxMinDiff = OpModelUtil.getMaxMinDiff(dataList);
        Integer workThreshold = OpModelUtil.getWorkThreshold(dataList, stop, opNum);
        System.out.println("workThreshold=" + workThreshold + " : maxMinDiff=" + maxMinDiff);

        // 5. 输出判断结果到excel
        outputHandleExcel(workbook, workThreshold, maxMinDiff, "./oee2_finish.xlsx");
    }

}
